package com.hsnn.medstgmini.base.std.dao.impl;

import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.base.std.dao.StdCompgroupDao;
import com.hsnn.medstgmini.base.std.model.StdCompgroup;
import com.hsnn.medstgmini.base.std.model.StdCompgroupLog;
import com.hsnn.medstgmini.base.std.model.StdCompinfo;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.model.Result;
import com.hsnn.medstgmini.model.Status;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseDate;
import com.hsnn.medstgmini.util.PinYin;

/**
 * A data access object (DAO) providing persistence and search support for
 * StdCompgroup entities. Transaction control of the save(), update() and
 * delete() operations can directly support Spring container-managed
 * transactions or they can be augmented to handle user-managed Spring
 * transactions. Each of these methods provides additional information for how
 * to configure it for the desired type of transaction control.
 * 
 * @see com.hsnn.medstgmini.base.std.model.StdCompgroup
 * @author MyEclipse Persistence Tools
 */
@SuppressWarnings("deprecation")
@Repository("StdCompgroupDao")
public class StdCompgroupDaoImpl extends BaseHibernateDAO implements StdCompgroupDao {
	
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	@Autowired
	private HibernateSqlUtil dao;
	
	private static final Logger log = LoggerFactory
			.getLogger(StdCompgroupDaoImpl.class);

	public void add(StdCompgroup transientInstance) {
		log.debug("saving StdCompgroup instance");
		try {
			getSession().save(transientInstance);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}



	public StdCompgroup getById(java.lang.String id) {
		log.debug("getting StdCompgroup instance with id: " + id);
		try {
			StdCompgroup instance = (StdCompgroup) getSession().get("com.hsnn.medstgmini.base.std.model.StdCompgroup", id);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}

	

	@SuppressWarnings("rawtypes")
	public List getByProperty(String propertyName, Object value) {
		log.debug("finding StdCompgroup instance with property: "+ propertyName + ", value: " + value);
		try {
			String queryString = "from StdCompgroup as model where model."+ propertyName + "= ?";
			Query queryObject = getSession().createQuery(queryString);
			queryObject.setParameter(0, value);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find by property name failed", re);
			throw re;
		}
	}

	@SuppressWarnings("unchecked")
	public List<StdCompgroup> getAll() {
		try {
			String queryString = "from StdCompgroup where status=1";
			Query queryObject = getSession().createQuery(queryString);
			return queryObject.list();
		} catch (RuntimeException re) {
			throw re;
		}
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<StdCompgroup> getQYCompgroup() {
		log.debug("finding QIYONG StdCompgroup instances");
		try {
			String queryString = "from StdCompgroup where status = 1 ";
			Query queryObject = getSession().createQuery(queryString);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}



	public void attachDirty(StdCompgroup instance) {
		Session session = sessionFactory.getCurrentSession();
		String uuid = UUID.randomUUID().toString();
		String compGroupCode = instance.getCompGroupCode();
		String compGroupName = instance.getCompGroupName();
		String compGroupShortname = instance.getCompGroupShortname();
		String remark = instance.getRemark();
		String updUser = instance.getUpdUser();
		StringBuffer sql = new StringBuffer()
		.append("insert into std_compgroup  ( status, comp_group_id ,comp_group_code ,comp_group_name , comp_group_shortname,remark ,upd_datetime,upd_user) ")
		.append(" values(1,:compGroupId,:compGroupCode, :compGroupName, :compGroupShortname, :remark, :updDatetime,:updUser)");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("compGroupId", uuid);
		query.setString("compGroupCode", compGroupCode);
		query.setString("compGroupName", compGroupName);
		query.setString("compGroupShortname", compGroupShortname);
		query.setString("remark", remark);	
		query.setString("updDatetime", ParseDate.parseFullFormat(new Date()));
		query.setString("updUser", updUser);
		query.executeUpdate();
		instance.setCompGroupId(uuid);
		session.flush();
		writeLog(instance);
		/*try {
			getSession().saveOrUpdate(instance);
		} catch (RuntimeException re) {
			throw re;
		}*/
	}

	

	
	/////////////////////////////////////////
	/**
	 * @category 保存集团库信息
	 * @param hospInfo
	 */
	public void saveCompgroup(StdCompgroup instance, int opType) {
		Session session = sessionFactory.getCurrentSession();
			if (opType == 1 && StringUtils.isNotBlank(instance.getCompGroupId())) {
				//StdCompgroup oldCompGroup = getCompGroupById(instance.getCompGroupId());
				//instance.setStatus(oldCompGroup.getStatus());
				//session.evict(oldCompGroup);
				StringBuffer sql = new StringBuffer()
				.append("	update std_compgroup a set 	")
				.append("	a.comp_group_id = ?,a.comp_group_code = ?,a.comp_group_name = ?,a.comp_group_shortname = ?,	")
				.append("	a.remark = ?	");
				Query query = session.createSQLQuery(sql.toString());
				
				query.setParameter(0, instance.getCompGroupId());
				query.setParameter(1, instance.getCompGroupCode());
				query.setParameter(2, instance.getCompGroupName());
				query.setParameter(3, instance.getCompGroupShortname());
				query.setParameter(4, instance.getRemark());
				query.executeUpdate();
				session.flush();
			} else {
				if (StringUtils.isBlank(instance.getCompGroupId())) {
					instance.setCompGroupId(UUID.randomUUID().toString());
				}
				instance.setStatus((short) Status.enabled.getKey());
				StringBuffer sql1 = new StringBuffer()
				.append("insert into std_compgroup values(?,?,?,?,?)");
				Query query1 = session.createSQLQuery(sql1.toString());
				query1.setParameter(0, instance.getCompGroupId());
				query1.setParameter(1, instance.getCompGroupCode());
				query1.setParameter(2, instance.getCompGroupName());
				query1.setParameter(3, instance.getCompGroupShortname());
				query1.setParameter(4, instance.getRemark());
			
				query1.executeUpdate();
			}
			//writeLog(instance.getCompGroupId());
	}

	
	/**
	 * 集团库 信息查询及条件查询
	 * 
	 * @param stdCompinfo
	 * @return
	 */
	public Pagination queryStdCompinfoList(Pagination page,StdCompgroup stdCompgroup) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.comp_group_id as compGroupId, a.comp_group_code as compGroupCode, a.comp_group_name as compGroupName,a.comp_group_spel_name as compGroupSpelName,")
			.append(" a.comp_group_shortname as compGroupShortname,a.comp_short_spel_code as compShortSpelCode,a.remark, a.status , a.upd_datetime as updDatetime, a.upd_user as updUser  ")
			.append(" FROM std_compgroup a where 1 = 1");
		Map<String, Object> param = new HashMap<String, Object>();
		
		//判断企业id
		if(StringUtils.isNotBlank(stdCompgroup.getCompGroupId())){
			sql.append(" and a.comp_group_id in (:compGroupIds) ");
			List<String> list = new ArrayList<String>();
			String[] strId = stdCompgroup.getCompGroupId().split(",");
			for(int i=0;i<strId.length;i++){
				list.add(strId[i]);
			}
			param.put("compGroupIds", list);
		}
		if (null != stdCompgroup.getCompGroupCode()&& !"".equals(stdCompgroup.getCompGroupCode())) {
			sql.append(" and a.comp_group_code = :compGroupCode");
			param.put("compGroupCode", stdCompgroup.getCompGroupCode().trim());
		}
		if (null != stdCompgroup.getCompGroupName()&& !"".equals(stdCompgroup.getCompGroupName())) {
			sql.append(" AND (a.comp_group_name  LIKE :compGroupName OR a.comp_group_spel_name LIKE :compGroupName ) ");
			param.put("compGroupName", "%" + stdCompgroup.getCompGroupName().trim()+ "%");
		}
		if (null != stdCompgroup.getStatus()&& !"".equals(stdCompgroup.getStatus())) {
			sql.append(" and a.status = :status");
			param.put("status", stdCompgroup.getStatus());
		}
		sql.append(" order by  a.comp_group_code desc ");
		return dao.getSqlPageMapList(sql.toString(), param, page);
	}
	/**
	 * 停用、启用
	 */
	@Override
	public int batchStarts(StdCompgroup instance) {
		Session session = getSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" update std_compgroup set ")
			.append(" status=:status,upd_datetime=:updDatetime,upd_user=:updUser ")
			.append(" where comp_group_id= :compGroupId and status <> :status ");
		Query update = session.createSQLQuery(sql.toString());
		update.setLong("status", instance.getStatus());
		update.setString("updUser", instance.getUpdUser());
		update.setTimestamp("updDatetime", new Date());
		update.setString("compGroupId", instance.getCompGroupId());
		int count = update.executeUpdate();
		session.flush();
		if(count>0){
			writeLog(instance);	
		}
		return count;
	}
	
	/**
	 * 写日志
	 * @category 
	 * @Auther 徐国鹏
	 * @date 2015年9月21日 上午9:41:22
	 * @param stdCompinfo
	 * @return void
	 */
	@Override
	public void writeLog(StdCompgroup instance){
		Session session = getSession();
		// ----begin	
        StringBuffer detail = new StringBuffer();
		StringBuffer sb = new StringBuffer()
		.append(" select comp_group_code,comp_group_name,comp_group_shortname,remark,status ")
		.append(" from std_compgroup where comp_group_id = :compGroupId ")
		.append(" union all ")
		.append(" select t.* from (select comp_group_code,comp_group_name,comp_group_shortname,remark,status ")
		.append(" from std_compgroup_log where comp_group_id = :compGroupId  order by upd_datetime desc limit 1 ) t");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compGroupId", instance.getCompGroupId());
		List<?> list = dao.getSqlList(sb.toString(), params);
		
		String[] colunm ={"集团编码","集团名称","集团简称","备注","状态"};
		if(list.size()==1){
			Object[] obj =(Object[]) list.get(0);
			obj[4] = obj[4]==null?"":"1".equals(obj[4].toString())?"启用":"停用";
			for(int i = 0;i<obj.length;i++){
				detail.append(";").append(colunm[i]).append(":").append(obj[i]==null?"":obj[i].toString());
			}
			detail.delete(0, 1);
		}else{
			Object[] obj1 =(Object[]) list.get(0);//修改后
			Object[] obj2 =(Object[]) list.get(1);//修改前
			obj1[4] = obj1[4]==null?"":"1".equals(obj1[4].toString())?"启用":"停用";
			obj2[4] = obj2[4]==null?"":"1".equals(obj2[4].toString())?"启用":"停用";
			for(int i = 0;i<obj1.length;i++){
				if(!(obj1[i]==null?"":obj1[i].toString()).equals(obj2[i]==null?"":obj2[i].toString())){
					detail.append(";").append(colunm[i]).append(":").append(obj2[i]==null?"":obj2[i].toString()).append(",").append(obj1[i]==null?"":obj1[i].toString());
				}
				
			}
			detail.delete(0, 1);
		}
		// ----end
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into std_compgroup_log(log_id,comp_group_id,comp_group_code,comp_group_name,comp_group_spel_name,comp_group_shortname,comp_short_spel_code, ")
			.append(" remark,status,upd_datetime,upd_user,detail ")
			.append(" ) select ")
		    .append(" UUID(),t.comp_group_id,t.comp_group_code,t.comp_group_name,t.comp_group_spel_name,t.comp_group_shortname,t.comp_short_spel_code,t.remark,t.status,t.upd_datetime,t.upd_user,:detail from std_compgroup t where t.comp_group_id= :compGroupId ");
		Query query=session.createSQLQuery(sql.toString());
		query.setString("compGroupId",instance.getCompGroupId());
		query.setString("detail",detail.toString());
		query.executeUpdate();
		session.flush();
		
	}
	/**
	 * 企业库 维护记录查询所有及条件查询
	 * @param stdCompinfoLog
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String,Object>> queryStdCompinfoLogByName(
			Pagination page,StdCompgroupLog stdCompgroupLog) {
		String compGroupId = stdCompgroupLog.getCompGroupId();
		String date1 = stdCompgroupLog.getDate1();
		String date2 = stdCompgroupLog.getDate2();
		Short status = stdCompgroupLog.getStatus();
		StringBuffer hql = new StringBuffer();
		StringBuffer countHql = new StringBuffer();
		hql.append(" select scl.detail,");
		hql.append(" scl.log_id logId,scl.comp_group_id compGroupId,scl.comp_group_code compGroupCode,scl.comp_group_name compGroupName,scl.comp_group_spel_name compGroupSpelName, ");
		hql.append(" scl.comp_group_shortname compGroupShortname,scl.comp_short_spel_code compShortSpelCode, ");
		hql.append(" scl.status,s.name updUser,DATE_FORMAT(scl.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetime  ");
		hql.append(" from std_compgroup_log scl ");
		hql.append(" LEFT JOIN sys_user s ON s.id=scl.upd_user ");
		hql.append(" where ");
		hql.append(" scl.comp_group_id = :compGroupId ");
		
		
		countHql.append(" select count(*) ");	
		countHql.append(" from std_compgroup_log scl ");
		countHql.append(" left join std_compgroup scg on scl.comp_group_id = scg.comp_group_id ");
		countHql.append(" where ");
		countHql.append(" scl.comp_group_id = :compGroupId ");
		if(StringUtils.isNotBlank(date1)){
		hql.append(" and scl.upd_datetime >= :date1 ");
		countHql.append(" and scl.upd_datetime >= :date1 ");
		}
		if(StringUtils.isNotBlank(date2)){
			hql.append(" and scl.upd_datetime <= :date2  ");
			countHql.append(" and scl.upd_datetime <= :date2  ");
		}
		if(null != status ){
			hql.append(" and scl.status = :status  ");
			countHql.append(" and scl.status = :status  ");
		}
		hql.append(" order by scl.upd_datetime desc  ");
		Query countQuery = getSession().createSQLQuery(countHql.toString());
		Query query = getSession().createSQLQuery(hql.toString());
		query.setString("compGroupId", compGroupId);
		countQuery.setString("compGroupId", compGroupId);
		if(StringUtils.isNotBlank(date1)){
				query.setString("date1", date1);
				countQuery.setString("date1", date1);
			}
		if(StringUtils.isNotBlank(date2)){
				query.setString("date2", date2);
				countQuery.setString("date2", date2);
			}
		if(null != status ){
			query.setShort("status", status);
			countQuery.setShort("status", status);
		}
		stdCompgroupLog.setCount(Long.valueOf(countQuery.uniqueResult().toString()));
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
	/**
	 * 详情
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getDicDrugformLogDetail(Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		String logId = page.getConditions().get("logId");
		StringBuffer  sql = new StringBuffer();
		sql.append(" SELECT a.log_id logId,a.comp_group_id compGroupId, a.comp_group_code compGroupCode, a.comp_group_name compGroupName, a.comp_group_spel_name compGroupSpelName,a.comp_group_shortname compGroupShortname, ")
		   .append(" a.comp_short_spel_code compShortSpelCode,a.remark,a.status,DATE_FORMAT(a.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetime,s.name updUser  ")
		   .append(" FROM  std_compgroup_log a ")
		   .append(" LEFT JOIN sys_user s ON s.id=a.upd_user  ")
		   .append(" WHERE a.upd_datetime <= (SELECT upd_datetime FROM std_compgroup_log where log_id = :logId)   ")
		   .append(" order by a.upd_datetime desc limit 2 ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("logId", logId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
	/////////////////////////////////////////

	@Override
	public void update(StdCompgroup sht) {
		Session session = sessionFactory.getCurrentSession();
		String compGroupId = sht.getCompGroupId();
		String compGroupCode = sht.getCompGroupCode();
		String compGroupName = sht.getCompGroupName();
		String compGroupShortname = sht.getCompGroupShortname();
		String remark = sht.getRemark();
		String updUser = sht.getUpdUser();
		StringBuffer sql = new StringBuffer()
		.append("update std_compgroup a set a.comp_group_code = :compGroupCode,a.comp_group_name = :compGroupName,a.comp_group_shortname = :compGroupShortname,a.remark = :remark,a.upd_datetime =:updDatetime,a.upd_user=:updUser where a.comp_group_id = :compGroupId ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("compGroupId", compGroupId);
		query.setString("compGroupCode", compGroupCode);
		query.setString("compGroupName", compGroupName);
		query.setString("compGroupShortname", compGroupShortname);
		query.setString("remark", remark);	
		query.setString("updDatetime", ParseDate.parseFullFormat(new Date()));
		query.setString("updUser", updUser);	
		query.executeUpdate();
		session.flush();
		writeLog(sht);
		/*try {
			getSession().update(sht);
			getSession().flush();
		} catch (RuntimeException re) {
			throw re;
		}*/
	}
	

	@Override
	public void log(StdCompgroup sht) {
		StdCompgroupLog groupLog=new StdCompgroupLog();
		groupLog.setCompGroupId(sht.getCompGroupId());
		groupLog.setCompGroupCode(sht.getCompGroupCode());
		groupLog.setCompGroupName(sht.getCompGroupName());
		groupLog.setCompGroupShortname(sht.getCompGroupShortname());
		groupLog.setCompGroupSpelName(sht.getCompGroupSpelName());
		groupLog.setCompShortSpelCode(sht.getCompShortSpelCode());
		groupLog.setStatus(sht.getStatus());
		groupLog.setRemark(sht.getRemark());

		groupLog.setUpdDatetime(sht.getUpdDatetime());
		groupLog.setUpdUser(sht.getUpdUser());
		log.debug("saving log instance");
		try {
			getSession().save(groupLog);
			getSession().flush();
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}

	@SuppressWarnings({ "unchecked" })
	@Override
	public List<StdCompgroupLog> getLogByResult(Result result, String tid,
			String startTime, String endTime) {

		log.debug("finding DicHosptype instance by Result and example");
		try {
			Criteria c= getSession().createCriteria("com.hsnn.medstgmini.base.std.model.StdCompgroupLog")
					.add(Restrictions.eq("compGroupId", tid));
			
					if(startTime!=null&&!startTime.equals("")){
						c.add(Restrictions.ge("updDatetime",ParseDate.fromFullFormat(startTime+" 00:00:00")));
					}
					if(endTime!=null&&!endTime.equals("")){
						c.add(Restrictions.le("updDatetime",ParseDate.fromFullFormat(endTime+" 23:59:59")));
					}
					List<StdCompgroupLog> results= (List<StdCompgroupLog>)c.setFirstResult(result.getStart())
					.setMaxResults(result.getPagesize())
					.addOrder(Order.desc("updDatetime")).list();
			log.debug("find by example successful, result size: "
					+ results.size());

			return results;
		} catch (RuntimeException re) {
			log.error("find by example failed", re);
			throw re;
		}
	}

	@Override
	public Long getSizeByResult(StdCompgroup scg) {
		Object results = getSession().createCriteria("com.hsnn.medstgmini.base.std.model.StdCompgroup")
				.setProjection(Projections.rowCount())
				.add(Restrictions.and(Restrictions.like("compGroupCode", scg.getCompGroupCode()==null?"":scg.getCompGroupCode(),MatchMode.ANYWHERE),Restrictions.like("compGroupName", scg.getCompGroupName()==null?"":scg.getCompGroupName(),MatchMode.ANYWHERE)))
				.uniqueResult();
		log.debug("find by example successful, result size: "
				+ results);
		if(results!=null){
			return Long.valueOf(results.toString());
		}
		return 0L;
	}

	@Override
	public Long getLogSizeByResult(Result result, String tid, String startTime,
			String endTime) {

		log.debug("finding LogSize instance by Result and example");
		try {
			
			Criteria c= getSession().createCriteria("com.hsnn.medstgmini.base.std.model.StdCompgroupLog")
					.setProjection(Projections.rowCount())
					.add(Restrictions.eq("compGroupId", tid));
			
					if(startTime!=null&&!startTime.equals("")){
						c.add(Restrictions.ge("updDatetime",ParseDate.fromFullFormat(startTime+" 00:00:00")));
					}
					if(endTime!=null&&!endTime.equals("")){
						c.add(Restrictions.le("updDatetime",ParseDate.fromFullFormat(endTime+" 23:59:59")));
					}

					Object results=c.uniqueResult();
			log.debug("find by example successful, result size: "
					+ results.toString());
			return Long.valueOf(results.toString());
		} catch (RuntimeException re) {
			log.error("find by example failed", re);
			throw re;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public boolean checkHosptypeCode(StdCompgroup instance) {
		List<StdCompgroup> dhts=this.getByProperty("compGroupCode",instance.getCompGroupCode());
		if(dhts.size()>0){
			if(instance.getCompGroupId()==null||!instance.getCompGroupId().equals(dhts.get(0).getCompGroupId())){
				return false;
			}
		}
		return true;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public boolean checkCompGroupName(StdCompgroup instance) {
		List<StdCompgroup> dhts=this.getByProperty("compGroupName",instance.getCompGroupName());
		if(dhts.size()>0){
			if(instance.getCompGroupId()==null||!instance.getCompGroupId().equals(dhts.get(0).getCompGroupId())){
				return false;
			}
		}
		return true;
	}
	
	/**
	 * 集团库  集团下属企业信息查看 关联下属企业  查询
	 * author zhouqi
	 * time 2015年8月17日09:50:51
	 * param page
	 * return Pagination
	 */
	@SuppressWarnings("unchecked")
	@Override
	public Pagination getStdCompinfoListByPage(Pagination page) {
		@SuppressWarnings("unused")
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.comp_id compId, A.comp_code compCode, A.org_code orgCode, A.comp_name compName,A.comp_spel_code compSpelCode,");
		sql.append(" A.comp_type compType,A.area_id areaId,A.comp_reg_id compRegId,A.status status,A.upd_datetime updDatetime,");
		sql.append(" B.ar_fullname arFullname, C.comp_reg_name compRegName, D.comp_group_id compGroupId, D.comp_group_name compGroupName");
		sql.append(" FROM std_compinfo A  LEFT JOIN dic_area B on A.area_id = B.area_id  LEFT JOIN dic_comptype C ");
		sql.append(" on C.comp_reg_id = A.comp_reg_id LEFT JOIN std_compgroup D ON A.comp_group_id = D.comp_group_id WHERE 1 = 1");
		@SuppressWarnings("rawtypes")
		Map<String,Object> params = new HashMap();
		if(StringUtils.isNotBlank(page.getConditions().get("compCode"))){
			sql.append(" and A.comp_code = :compCode");
			params.put("compCode", page.getConditions().get("compCode"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("compName"))){
			if(PinYin.isPinYin(page.getConditions().get("compName"))){
				sql.append(" and A.comp_spel_code like :compName ");
			}else{
				sql.append(" and A.comp_name like :compName");
			}
			params.put("compName", "%"+page.getConditions().get("compName")+"%");//compGroupId
		}
		if(StringUtils.isNotBlank(page.getConditions().get("compGroupId"))){
			sql.append(" and A.comp_group_id = :compGroupId");
			params.put("compGroupId", page.getConditions().get("compGroupId"));
		}
		if(null == page.getConditions().get("compGroupId")){
			sql.append(" and A.comp_group_id is null");
		}
        return  dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 集团库  集团下属企业信息查看 关联下属企业  查询
	 * author zhouqi
	 * time 2015年8月17日09:50:51
	 * param page
	 * return Pagination
	 */
	@Override
	public Pagination getStdCompinfoListButHB(Pagination page) {  
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.comp_id compId, A.comp_code compCode, A.org_code orgCode,A.comp_name compName,A.comp_spel_code compSpelCode,");
		sql.append(" A.comp_type compType,A.area_id areaId,A.comp_reg_id compRegId,A.status status,A.upd_datetime updDatetime,");
		sql.append(" B.ar_fullname arFullname, C.comp_reg_name compRegName, D.comp_group_id compGroupId, D.comp_group_name compGroupName");
		sql.append(" FROM std_compinfo A  LEFT JOIN dic_area B on A.area_id = B.area_id  LEFT JOIN dic_comptype C ");
		sql.append(" on C.comp_reg_id = A.comp_reg_id LEFT JOIN std_compgroup D ON A.comp_group_id = D.comp_group_id WHERE 1 = 1 AND A.status  <>3 ");
		Map<String,Object> params = new HashMap<String,Object>();
		if(StringUtils.isNotBlank(page.getConditions().get("compCode"))){
			sql.append(" and A.comp_code = :compCode");
			params.put("compCode", page.getConditions().get("compCode"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("compName"))){
			if(PinYin.isPinYin(page.getConditions().get("compName"))){
				sql.append(" and A.comp_spel_code like :compName");
			}else{
				sql.append(" and A.comp_name like :compName");
			}
			
			params.put("compName", "%"+page.getConditions().get("compName")+"%");//compGroupId
		}
		if(StringUtils.isNotBlank(page.getConditions().get("compGroupId"))){
			sql.append(" and A.comp_group_id = :compGroupId");
			params.put("compGroupId", page.getConditions().get("compGroupId"));
		}
		if(null == page.getConditions().get("compGroupId")){
			sql.append(" and A.comp_group_id is null");
		}
        return  dao.getSqlPageMapList(sql.toString(), params, page);
	}

	@Override
	public long getStdCompinfoListSizeByPage(Pagination page) {
		log.debug("finding StdCompgroup List by Result and example");
		try{
			Criteria c = getSession().createCriteria("com.hsnn.medstgmini.base.std.model.StdCompinfo");
			c.setProjection(Projections.rowCount());
			Map<String, String> conditions = page.getConditions();
			for (String key : conditions.keySet()) {
				 if(key.equals("compCode")||key.equals("compName")){
					 c.add(Restrictions.like(key,conditions.get(key),MatchMode.ANYWHERE));
				 }else if(conditions.get(key)==null){
					 c.add(Restrictions.isNull(key));
				 }else{
					 c.add(Restrictions.eq(key,conditions.get(key)));
				 }
			 }
			 /*String compCode = conditions.get("compCode");
			 if (StringUtils.isNotBlank(compCode)) {
				 c.add(Restrictions.eq("compCode", compCode));
			 }
			 String compName = conditions.get("compName");
			 if (StringUtils.isNotBlank(compName)) {
				 c.add(Restrictions.like("compName", compName, MatchMode.ANYWHERE));
			 }*/
			 if(page.getMaxResults()!=0){
					c.setFirstResult(page.getFirstResult())
					.setMaxResults(page.getMaxResults());
			 }
			Object results=c.addOrder(Order.asc("compCode")).uniqueResult();;
			
			log.debug("find by example successful, result size: "+ results);
			if(results!=null){
					return Long.valueOf(results.toString());
			}
			return 0L;
		} catch (RuntimeException re) {
			log.error("find by example failed", re);
			throw re;
		}
	}
	

	@Override
	public Object batchGroupAddComp(List<StdCompinfo> list) {
		String hql="update StdCompinfo set compGroupId = :compGroupId where compId = :compId";
		for(StdCompinfo sc:list){
			Query query = getSession().createQuery(hql);
			query.setString("compGroupId", sc.getCompGroupId());
			query.setString("compId", sc.getCompId());
			query.executeUpdate();
		}
		getSession().flush();
		return null;
	}
	
	@Override
	public void exportExcel(Pagination page,OutputStream os){
        XSSFWorkbook wb = new XSSFWorkbook();  
        XSSFSheet sheet = wb.createSheet("集团");  
        XSSFRow row = sheet.createRow((int) 0);  
        XSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER); 
        
        XSSFCell cell = null;
        String[] headers = {"集团编码","集团名称","集团简称","备注","状态"};
        for(int i = 0;i<headers.length;i++){
        	 cell = row.createCell(i);
             cell.setCellValue(headers[i]);  
             cell.setCellStyle(style);  
        }
        
        List<Object> list = getFormNoPage(page);
        for (int i = 0; i < list.size(); i++)  
        {  
            row = sheet.createRow((int) i + 1);  
            Object[] obj = (Object[]) list.get(i);  
            for(int j = 0;j<obj.length;j++){
            	row.createCell(j).setCellValue(obj[j]==null?"":obj[j].toString());  
            }
            
        }  
        try {   
            //OutputStream os = new FileOutputStream("D:/Experttype.xls");  
            wb.write(os);  
            os.close();  
            page.setSuccess(true);
        } catch (Exception e){  
           page.setSuccess(false);
            e.printStackTrace();  
        } 
       
	}

	@SuppressWarnings("unchecked")
	private List<Object> getFormNoPage(Pagination page){
		String compGroupCode = page.getConditions().get("compGroupCode");
		String compGroupName = page.getConditions().get("compGroupName");
		String compGroupShortname = page.getConditions().get("compGroupShortname");
		String remark = page.getConditions().get("remark");
		String status = page.getConditions().get("status");
		StringBuffer sql = new StringBuffer()
		.append(" select  comp_group_code compGroupCode, comp_group_name compGroupName,comp_group_shortname compGroupShortname,remark remark,case status when 0 then '停用' else '启用' end  status ")
		.append(" from std_compgroup where 1=1");
		if (StringUtils.isNotBlank(compGroupCode)) {
			sql.append(" and comp_group_code = :compGroupCode ");
		}
		if (StringUtils.isNotBlank(compGroupName)) {
			sql.append(" and comp_group_name like :compGroupName ");
		}
		if (StringUtils.isNotBlank(compGroupShortname)) {
			sql.append(" and comp_group_shortname like :compGroupShortname ");
		}
		if (StringUtils.isNotBlank(remark)) {
			sql.append(" and remark = :remark");
		}
		if (StringUtils.isNotBlank(status)) {
			sql.append(" and status = :status");
		}

		Query query = getSession().createSQLQuery(sql.toString());
		if (StringUtils.isNotBlank(compGroupCode)) {
			query.setParameter("compGroupCode",  compGroupCode );
		}
		if (StringUtils.isNotBlank(compGroupName)) {
			query.setParameter("compGroupName", "%" + compGroupName + "%");
		}
		if (StringUtils.isNotBlank(compGroupShortname)) {
			query.setParameter("compGroupShortname", "%" + compGroupShortname + "%");
		}
		if (StringUtils.isNotBlank(remark)) {
			query.setParameter("remark",  remark );
		}
		if (StringUtils.isNotBlank(status)) {
			query.setParameter("status", status);
		}
		return query.list();
	}







}